package com.tjsinfo.tjsoa.common;

import java.util.Map;

public class PagenatedSql {
	/**
	 * @根据传入带有参数的SQL生成可执行的SQL语句
	 * @param parameterNames
	 *            页面中传递过来的�?有参�?
	 * @param sql
	 *            要处理的SQL语句
	 * @return String
	 */
	public static String buildSQL(Map<String,String> parameterNames,String sql){
		String tempSelectTopArray[]=sql.split("#");
		for (int i=1;i<tempSelectTopArray.length;i=i+2) {
			String temp=tempSelectTopArray[i];
			sql=sql.replace("#"+temp+"#", "'"+parameterNames.get(temp)+"'");
			//sql=sql.replace("'"+temp+"'", "'"+parameterNames.get(temp)+"'");
		}
		return sql;
	}
	/**
	 * @根据SQL语句查询符合条件的记录数
	 * @param sql
	 *            要处理的SQL语句
	 * @param countName
	 *            返回结果集数量的名称
	 * @return String
	 */
	public static String buildCountSQL(String sql,String countName){
		String temp[]=sql.split("from");
		sql=sql.replace(temp[0], "select count(*) as "+countName+" ");
		if(sql.contains("order")){sql=sql.substring(0,sql.indexOf("order"));};
		if(sql.contains("group")){sql=sql.substring(0,sql.indexOf("group"));};
		return sql;
	}
	/**
	 * @根据带有参数的SQL以及要查询的单页记录数和页码生成sql语句
	 * @param sql
	 *            要查询的SQL语句
	 * @param pageSize
	 *            每页的记录数
	 * @param startIndex
	 *            要查询的其实位置
	 * @return String
	 */
	public static String buildSQLResultSet(String sql,int pageSize,int startIndex){
		StringBuffer topSql=new StringBuffer();
		boolean isFirstPage = startIndex<pageSize?true:false;//是否为第�?�?
		if(isFirstPage){
			sql=sql.replace("select", "select top "+pageSize);
		}else{
			sql=sql.replace("select", "select top "+pageSize);
			String oldSQL[]=sql.split("where");
			
			String fromSql[]=sql.split("from");//判断SQL是否为单�?
			boolean isMoreTable=fromSql[1].contains(" as ")?true:false;
			if(isMoreTable){
				String asTableName=fromSql[1].substring(fromSql[1].indexOf(" as ")+4, fromSql[1].indexOf(","));
				asTableName=asTableName.replace(" ", "");
				topSql.append(asTableName);
				topSql.append(".id NOT IN (select top ");
				topSql.append(startIndex);
				topSql.append(" ");
				topSql.append(asTableName);
				topSql.append(".id from");
				topSql.append(fromSql[1]);
				topSql.append(")");
			}else{
				topSql.append("id NOT IN (select top ");
				topSql.append(startIndex);
				topSql.append(" ");
				topSql.append("id from");
				topSql.append(fromSql[1]);
				topSql.append(")");
			}
			
			sql=oldSQL[0]+"where ("+topSql+") and "+oldSQL[1];
		}
		return sql;
	}
	/**
	 * @根据带有参数的SQL以及要查询的单页记录数和页码生成sql语句
	 * @param sql
	 *            要查询的SQL语句
	 * @param pageSize
	 *            每页的记录数
	 * @param startIndex
	 *            要查询的其实位置
	 * @param id
	 *            如果主键不是ID
	 * @return String
	 */
	public static String buildSQLResultSet(String sql,String id,int pageSize,int startIndex){
		StringBuffer topSql=new StringBuffer();
		boolean isFirstPage = startIndex<pageSize?true:false;//是否为第�?�?
		if(isFirstPage){
			sql=sql.replace("select", "select top "+pageSize);
		}else{
			sql=sql.replace("select", "select top "+pageSize);
			String oldSQL[]=sql.split("where");
			
			String fromSql[]=sql.split("from");//判断SQL是否为单�?
			boolean isMoreTable=fromSql[1].contains(" as ")?true:false;
			if(isMoreTable){
				String asTableName=fromSql[1].substring(fromSql[1].indexOf(" as ")+4, fromSql[1].indexOf(","));
				asTableName=asTableName.replace(" ", "");
				topSql.append(asTableName);
				topSql.append("."+id+" NOT IN (select top ");
				topSql.append(startIndex);
				topSql.append(" ");
				topSql.append(asTableName);
				topSql.append("."+id+" from");
				topSql.append(fromSql[1]);
				topSql.append(")");
			}else{
				topSql.append(id+" NOT IN (select top ");
				topSql.append(startIndex);
				topSql.append(" ");
				topSql.append(id+" from");
				topSql.append(fromSql[1]);
				topSql.append(")");
			}
			
			sql=oldSQL[0]+"where ("+topSql+") and "+oldSQL[1];
		}
		return sql;
	}
}
